"""科目余额表"""

import re
from datetime import datetime, date, timedelta

from odoo import models, api, fields
from odoo.tools import float_compare
from odoo.exceptions import UserError, ValidationError



class view(models.Model):
    _inherit = 'ir.ui.view'

    type = fields.Selection(selection_add=[('psreport', '报表')])


class Account(models.Model):
    _inherit = 'account.account'
    _parent_name = 'parent_id'

    parent_id = fields.Many2one('account.account', string="上级科目")
    child_ids = fields.One2many('account.account', 'parent_id', string="下级科目")
    level = fields.Integer('级数', compute="_compute_level", store=True)
    direction = fields.Selection([('credit', '贷方'), ('debit', '借方')], string="余额方向")
    show_dimension = fields.Boolean('显示核算维度')
    show_balance = fields.Boolean('在科目余额表里显示', default=True)
    allow_negative = fields.Boolean('允许显示负值')
    is_bottom_account = fields.Boolean('是否底层科目', compute="_compute_is_bottom_account",
                                       store=True)

    @api.depends('parent_id', 'parent_id.level')
    def _compute_level(self):
        for account in self:
            account.level = (account.parent_id.level or 0) + 1

    @api.depends('child_ids')
    def _compute_is_bottom_account(self):
        for acc in self:
            acc.is_bottom_account = not bool(acc.child_ids)


class AccountMoveLine(models.Model):
    _inherit = 'account.move.line'

    nd_state = fields.Selection(related="move_id.state", store=True)


class AccountBalance(models.TransientModel):
    _name = 'nd.account.balance'
    _description = "科目余额"

    def _get_level_selection(self):
        return [(i, f'{i}级') for i in range(1, 5)]

    period_id_from = fields.Date('开始期数')
    period_id_to = fields.Date('结束期数')
    account_id_from = fields.Many2one('account.account', string="开始科目")
    account_id_to = fields.Many2one('account.account', string="结束科目")
    account_level = fields.Selection(selection=_get_level_selection, string='科目等级')
    is_posted = fields.Boolean('包含未记账凭证')
    is_without_amount = fields.Boolean('无发生额无余额不显示')
    is_with_dimension = fields.Boolean('显示核算维度')

    @api.model
    def nd_report_fields_get(self, field_name, model_name):
        model = self.env[model_name]
        result = []
        for info in field_name:
            attrs = info['attrs']
            attrs.update({
                 'can_create': True,
                 'can_write': True,
            })
            field = model._fields[attrs['name']]
            data = {
                'field': {
                    'tag': info['tag'],
                    'attrs': attrs,
                },
                'field_description': attrs.get('string', attrs['name']),
                'relation': field.comodel_name,
                'ttype': field.type,
            }
            result.append(data)
        return result

    def _to_date(self, str_date):
        input_datetime = datetime.strptime(str_date, '%Y年%m月%d日')
        return date(input_datetime.year, input_datetime.month, input_datetime.day)

    @api.model
    def get_amount_action(self, acc_code, column_index, domain_fields, partner_name):
        """返回点击某个数字需要执行的动作字典"""
        start_date = self._to_date(domain_fields['period_id_from'])
        end_date = self._to_date(domain_fields['period_id_to'])
        is_posted = domain_fields['is_posted']
        account_id_from = account_id_to = self.env['account.account'].search([('code', '=', acc_code)], limit=1)
        is_with_dimension = domain_fields['is_with_dimension']
        term_init_index, this_term_index, this_year_index, term_end_index = self.get_column_index(is_with_dimension)
        child_account_ids = self.env['account.account'].search([('id', 'child_of', account_id_from.ids)])
        ids = []
        for key, index in enumerate([term_end_index, this_year_index, this_term_index, term_init_index]):
            if column_index >= index:
                this_term_data, this_year_data, term_init_data, term_end_data = self._get_data(start_date, end_date,
                                                                                               child_account_ids.ids,
                                                                                               is_posted,
                                                                                               is_with_dimension, single_click=True)
                raw_ids = [term_end_data, this_year_data, this_term_data, term_init_data][key]
                ids = [raw[0] for raw in raw_ids]
                break
        if partner_name:
            domain = [('id', 'in', ids), ('partner_id.name', '=', partner_name)]
            # if not partner_name:
            #     domain = [('id', 'in', ids), ('partner_id', '=', False)]
            lines = self.env['account.move.line'].search(domain)
            ids = lines.ids
        # action = self.env.ref('account.action_account_moves_all_a').read()[0]
        action = self.env.ref('nd_account_balance.nd_show_balance_detail').read()[0]
        action['views'] = [[self.env.ref('nd_account_balance.view_number_line_detail').id, 'list'], [False, 'form']]
        action['view_type'] = 'list'
        action['context'] = {}
        action['domain'] = [('id', 'in', ids)]
        action['target'] = 'new'
        action['search_view'] = False
        action['res_id'] = False
        action['name'] = '凭证'
        return action

    def get_column_index(self, show_dimension):
        """返回各个项的列索引"""
        term_init_index = 2  # 期初余额
        if show_dimension:
            term_init_index = 3
        this_term_index = term_init_index + 2  # 本期金额
        this_year_index = this_term_index + 2  # 本年金额
        term_end_index = this_year_index + 2  # 期末余额
        return term_init_index, this_term_index, this_year_index, term_end_index

    @api.model
    def get_report_data(self, render_type, domain_fields):
        account_start_id = self.env['account.account'].search([], limit=1)
        account_end_id = self.env['account.account'].search([], limit=1, order="code desc, id desc")
        default_start_date = fields.Date.today().replace(day=1)
        default_end_date = fields.Date.today()
        template = {
            'render_type': 'table',
            'rows': [{
                'act_domains': [],
                'act_model': '',
                'act_title': '',
                'column_names_multi': [
                    ["科目编号", "科目名称", "期初金额余额", "本期金额发生", "本年金额累计", "期末金额余额"],
                    ["借方", "贷方", "借方", "贷方", "借方", "贷方", " 借方", "贷方"]],
                'title': '科目余额表查询',
                'download_name': ['科目余额表查询'],
                'line_rows': [
                    ["1001", "库存现金", "", "","", "", "", "", "", ""],
                    ["1002", "银行存款", "", "","", "", "", "", "", ""],
                    # ...
                ],
                'merge_column_define_multi': [
                    {0: {'rs': 2}, 1: {'rs': 2}, 2: {'cs': 2}, 3: {'cs': 2}, 4: {'cs': 2}, 5: {'cs': 2}},
                    {}
                ],
                'row_al_define': {0: "left", 1: "left", 2: "right", 3: "right", 4: "right", 5: "right"},
                'column_names': [],
                'foot': {},
                'top': [{'期间': '', '科目': ''}]
            }],
            # 'domain': {
            #     'period_id_from': ['company_id', '=', 1],
            #     'period_id_to': ['company_id', '=', 1],
            # },
            'defaults': {
                'account_id_from': account_start_id.id,
                'account_id_to': account_end_id.id,
                'period_id_from': default_start_date,
                'period_id_to': default_end_date,
            },
            'thead_hidden': False}
        if domain_fields:
            try:
                start_date = self._to_date(domain_fields['period_id_from'])
                end_date = self._to_date(domain_fields['period_id_to'])
                account_level = domain_fields['account_level']
                is_posted = domain_fields['is_posted']
                account_id_from = domain_fields['account_id_from']
                account_id_to = domain_fields['account_id_to']
                account_id_from_code = self.env['account.account'].browse(account_id_from).code
                account_id_to_code = self.env['account.account'].browse(account_id_to).code
                account_ids = self.env['account.account'].search([('code', '>=', account_id_from_code),
                                                                  ('code', '<=', account_id_to_code)])
                is_without_amount = domain_fields['is_without_amount']
                is_with_dimension = domain_fields['is_with_dimension']
            except KeyError as e:
                raise UserError(f'缺少查询参数：{e}')
            except ValueError as e:
                raise UserError(f'日期不正确：{e}')

            this_term_data, this_year_data, term_init_data, term_end_data = self._get_data(start_date, end_date, account_ids.ids, is_posted, is_with_dimension)
            # {'1001': ["1001", "库存现金", "核算维度", "期初借", "期初贷", "本期借", "本期贷", "本年借", "本年贷", "期末借", "期末贷"]}
            if is_with_dimension:
                template['rows'][0]['column_names_multi'][0].insert(2, '核算维度')
                template['rows'][0]['merge_column_define_multi'][0][2] = {'rs': 2}
                template['rows'][0]['merge_column_define_multi'][0][6] = {'cs': 2}
                template['rows'][0]['row_al_define'][6] = "right"
                template['rows'][0]['row_al_define'][2] = "left"

            term_init_index, this_term_index, this_year_index, term_end_index = self.get_column_index(is_with_dimension)

            raw_list_factory = lambda: is_with_dimension and [''] * 11 or [''] * 10
            # def raw_list_factory():
            #     if is_with_dimension:
            #         return ['', '', '', 0, 0, 0, 0, 0, 0, 0, 0]
            #     return ['', '', 0, 0, 0, 0, 0, 0, 0, 0]
            def add_it(origin_num, num, round_it=False):
                if num == '' and origin_num == '':
                    return ''
                if origin_num == '':
                    origin_num = 0
                if num == '':
                    num = 0
                return round(origin_num + num, 2)
            line_rows_data = {}
            def row_key_lambda(acc_code, partner_name, show_dimension):
                if is_with_dimension and show_dimension:
                    return f'{acc_code}-{partner_name}'
                return acc_code
            if is_without_amount:
                this_term_data.sort(key=lambda x: x[0])
                # 显示有发生额的
                for term_data in this_term_data:
                    show_dimension = term_data[5][0]
                    acc_code = term_data[0]
                    acc_name = term_data[1]
                    data = line_rows_data.setdefault(row_key_lambda(acc_code, term_data[4], show_dimension), raw_list_factory())
                    data[0] = acc_code
                    data[1] = acc_name
                    data[this_term_index] = add_it(data[this_term_index], term_data[2])
                    data[this_term_index+1] = add_it(data[this_term_index+1], term_data[3])
                    if is_with_dimension and show_dimension:
                        data[2] = term_data[4]
                    # data = [term_data[0], term_data[1], "", "", term_data[2], term_data[3], "", "", "", ""]
                    # line_rows_data[] = data
                # 显示有余额的
                for init_data in term_init_data:
                    # code, name, balance, [direction], partner.name, [show_dimension]
                    show_dimension = init_data[5][0]
                    key = row_key_lambda(init_data[0], init_data[4], show_dimension)
                    if key not in line_rows_data:
                        data = line_rows_data.setdefault(key, raw_list_factory())
                        data[0] = init_data[0]
                        data[1] = init_data[1]
                        if is_with_dimension and show_dimension:
                            data[2] = init_data[4]
            else:
                for term_data in this_term_data:
                    show_dimension = term_data[5][0]
                    data = line_rows_data.setdefault(row_key_lambda(term_data[0], term_data[4], show_dimension), raw_list_factory())
                    data[0] = term_data[0]
                    data[1] = term_data[1]
                    data[this_term_index] = add_it(data[this_term_index], term_data[2])
                    data[this_term_index+1] = add_it(data[this_term_index+1], term_data[3])
                    if is_with_dimension and show_dimension:
                        data[2] = term_data[4]
                for account_id in self.env['account.account'].search([('show_balance', '=', True)]):
                    # 补全没有本期发生额的科目余额信息
                    for key in line_rows_data.keys():
                        if key.startswith(account_id.code):
                            break
                    else:
                        data = raw_list_factory()
                        data[0] = account_id.code
                        data[1] = account_id.name
                        line_rows_data[account_id.code] = data
                        # line_rows_data[account_id.code] = [account_id.code, account_id.name, "", "", "", "", "", "", "", ""]

            for year_data in this_year_data:
                # 这里要注意对于那些没有本期发生额的，key就是科目代码，否则就是科目代码+客户名称
                show_dimension = year_data[5][0]
                data = line_rows_data.get(row_key_lambda(year_data[0], year_data[4], show_dimension))
                if not data:
                    data = line_rows_data.get(year_data[0])
                if data:
                    data[this_year_index] = add_it(data[this_year_index], year_data[2])
                    data[this_year_index + 1] = add_it(data[this_year_index + 1], year_data[3])

            for init_data in term_init_data:
                # 这里要注意对于那些没有本期发生额的，key就是科目代码，否则就是科目代码+客户名称
                show_dimension = init_data[5][0]
                data = line_rows_data.get(row_key_lambda(init_data[0], init_data[4], show_dimension))
                if not data:
                    data = line_rows_data.get(init_data[0])
                if data:
                    if (init_data[3][0] or '') == 'credit':
                        data[term_init_index + 1] = add_it(data[term_init_index + 1], init_data[2])
                    else:
                        data[term_init_index] = add_it(data[term_init_index], init_data[2])

            for end_data in term_end_data:
                # 这里要注意对于那些没有本期发生额的，key就是科目代码，否则就是科目代码+客户名称
                show_dimension = end_data[5][0]
                data = line_rows_data.get(row_key_lambda(end_data[0], end_data[4], show_dimension))
                if not data:
                    data = line_rows_data.get(end_data[0])
                if data:
                    if (end_data[3][0] or '') == 'credit':
                        data[term_end_index + 1] = add_it(data[term_end_index + 1], end_data[2])
                    else:
                        data[term_end_index] = add_it(data[term_end_index], end_data[2])
            # 构建上下级
            self.construct_level(line_rows_data, account_level, raw_list_factory)
            # 去掉正负号
            # allow_negative_info = {acc.code: acc.allow_negative for acc in self.env['account.account'].search([])}
            def abs_it(num, acc_code):
                # if isinstance(num, (int, float)):
                #     allow_negative = allow_negative_info.get(acc_code)
                #     if not allow_negative:
                #         return abs(num)
                return num
            def add_delimiter(data_entry):
                # 添加千分号 123456 -> 123,456
                for k, v in enumerate(data_entry):
                    if k >= term_init_index and isinstance(v, (int, float)):
                        data_entry[k] = format(v, ',')
            for k, v in line_rows_data.items():
                v[term_init_index] = abs_it(v[term_init_index], v[0])
                v[term_init_index+1] = abs_it(v[term_init_index+1], v[0])
                v[term_end_index] = abs_it(v[term_end_index], v[0])
                v[term_end_index+1] = abs_it(v[term_end_index+1], v[0])
                add_delimiter(v)
            # 增加汇总项
            combine_text = '合计'
            def sorted_lambda(row_data):
                priority = 1
                if is_with_dimension and row_data[2] == combine_text:
                    priority = 0
                return row_data[0], priority, row_data[1]

            template['rows'][0]['line_rows'] = sorted(list(line_rows_data.values()), key=sorted_lambda)
            account_id_from_id = self.env['account.account'].browse(account_id_from)
            account_id_to_id = self.env['account.account'].browse(account_id_to)
            template['rows'][0]['top'] = [{
                '期间': f'{start_date} - {end_date}',
                '科目': f'{account_id_from_id.code or ""} - {account_id_to_id.code or ""}'}]

        return template

    def construct_level(self, line_rows_data, level, raw_list_factory):
        """显示特定层级的科目，将下级科目金额向上级汇总"""
        level = int(level)
        # [[code, level, p_acc.code, name]]
        level_info = self.get_account_level_info()
        # {'code': [level, parent_id_code, name]}
        level_info_dict = {x[0]: x[1:] for x in level_info}
        def get_data(code):
            if code:
                if code in line_rows_data:
                    return line_rows_data[code]
                for k, v in line_rows_data.items():
                    if k.startswith(f'{code}-'):
                        return v
        def get_level(data_entry):
            # data_entry: [code, name, '维度', ...]
            return level_info_dict[data_entry[0]][0]
        def get_parent_code(code):
            return level_info_dict[code][1]
        def safe_add_num(origin_num, num):
            if num == '' and origin_num == '':
                return ''
            if origin_num == '':
                origin_num = 0
            if num == '':
                num = 0
            return round(origin_num + num, 2)
        def add_child_to_parent(child_data, parent_data):
            # [code, name, '维度', 期初借, 期初贷, 本期借, 本期贷, 本年借, 本年贷, 期末借, 期末贷,]
            if len(child_data) == 11:
                rng = [3, 4, 5, 6, 7, 8, 9, 10]
            else:
                rng = [2, 3, 4, 5, 6, 7, 8, 9]
            for index in rng:
                parent_data[index] = safe_add_num(parent_data[index], child_data[index])

        # 先补齐数据里已有科目的全部上级科目
        for key, value in sorted(line_rows_data.copy().items(), key=lambda x: get_level(x[1]), reverse=True):
            parent_code = get_parent_code(value[0])
            parent_data = get_data(parent_code)
            while parent_code and not parent_data:
                default_parent_data = raw_list_factory()
                default_parent_data[0] = parent_code
                default_parent_data[1] = level_info_dict[parent_code][2]
                line_rows_data[parent_code] = default_parent_data
                parent_code = get_parent_code(parent_code)
                parent_data = get_data(parent_code)

        # 再将下级值累积到上级去，这个依赖于按层级进行排序。
        for key, value in sorted(line_rows_data.copy().items(), key=lambda x: get_level(x[1]), reverse=True):
        # for value in sorted(line_rows_data.copy().values(), key=get_level, reverse=True):
            lower_than_request = get_level(value) > level
            parent_data = get_data(get_parent_code(value[0]))
            if parent_data:
                add_child_to_parent(value, parent_data)
            # 如果低于当前请求的层级，则删除
            if lower_than_request:
                del line_rows_data[key]

        # 再将科目前添加空格以显示层级关系，并去掉正负号
        for key, value in line_rows_data.items():
            level = get_level(value)
            value[1] = f'<p>{"&nbsp;" * 4 * (level-1)}{value[1]}</p>'

    def get_account_level_info(self):
        sql = """
            SELECT aa.code, aa.level, p_acc.code, aa.name
            FROM account_account AS aa
            LEFT JOIN account_account AS p_acc
            ON p_acc.id = aa.parent_id
            WHERE aa.show_balance = 'true'
        """
        self.env.cr.execute(sql)
        datas = self.env.cr.fetchall()
        return datas

    def _get_data(self, start, end, account_ids, is_posted, is_with_dimension, single_click=False):
        # single_click 表示当前查询是用于响应点击单元格时收集相关凭证，所以只需要ID
        # start_account_code = self.env['account.account'].browse(account_id_from).code or '0'
        # end_account_code = self.env['account.account'].browse(account_id_to).code or '0'
        account_ids = tuple(account_ids)

        if single_click:
            select_query = """SELECT aml.id """
        else:
            select_query = """SELECT aa.code, aa.name, sum(aml.debit), sum(aml.credit), partner.name, array_agg(distinct(aa.show_dimension)) AS show_dimension """
        from_query = "FROM account_move_line AS aml " \
                     "LEFT JOIN account_account AS aa " \
                     "ON aa.id = aml.account_id " \
                     "LEFT JOIN res_partner AS partner " \
                     "ON aml.partner_id = partner.id "
        where_query = "WHERE aa.id in %s AND aml.date BETWEEN %s AND %s AND aa.show_balance = 'true' "
        if single_click:
            group_query = """ """
        else:
            group_query = "GROUP BY aa.code, aa.name, partner.name "
        if not is_posted:
            where_query += " AND aml.nd_state = 'posted' "

        QUERY = select_query + from_query + where_query + group_query
        # 本期发生额
        self.env.cr.execute(QUERY, [account_ids, start, end])
        this_term_data = self.env.cr.fetchall()

        # 本年发生额
        year_start, year_end = self._get_this_year_range(end)
        self.env.cr.execute(QUERY, [account_ids, year_start, year_end])
        this_year_data = self.env.cr.fetchall()

        if single_click:
            select_query = """SELECT aml.id """
        else:
            # select_query = 'SELECT aa.code AS code, aa.name AS name, sum(aml.balance) AS balance, array_agg(distinct(aa.direction)) AS direction, partner.name, array_agg(distinct(aa.show_dimension)) AS show_dimension '
            select_query = """SELECT aa.code AS code, aa.name AS name, 
                                CASE
                                    WHEN direction = 'credit' THEN sum(aml.credit - aml.debit)
                                    WHEN direction = 'debit' THEN sum(aml.debit - aml.credit)
                                END AS balance, 
                                array_agg(distinct(aa.direction)) AS direction, partner.name,
                                 array_agg(distinct(aa.show_dimension)) AS show_dimension """

        where_query = "WHERE aa.id in %s AND aml.date < %s AND aa.show_balance = 'true' "
        if single_click:
            group_query = """ """
        else:
            group_query = "GROUP BY aa.code, aa.name, aa.direction, partner.name "
        QUERY = select_query + from_query + where_query + group_query
        # 期初余额
        self.env.cr.execute(QUERY, [account_ids, start])
        term_init_data = self.env.cr.fetchall()

        # 期末余额
        where_query = "WHERE aa.id in %s AND aml.date <= %s AND aa.show_balance = 'true' "
        QUERY = select_query + from_query + where_query + group_query
        self.env.cr.execute(QUERY, [account_ids, end])
        term_end_data = self.env.cr.fetchall()
        return this_term_data, this_year_data, term_init_data, term_end_data

    def _get_this_year_range(self, end_date):
        """根据指定的截止日期，获取所在年份的起始日期到指定的截止元组"""
        year = end_date.year
        start = date(year, 1, 1)
        # end = date(year+1, 1, 1) - timedelta(days=1)
        return start, end_date

    # def _construct_tree_structure(self, sql_data):
    #     """
    #     将从数据库读取的科目余额，按照上下级关系构造树状结构
    #     :param sql_data: [(aa.code, aa.name, debit, credit, level, parent_id), ...]
    #     :return:
    #     """
    #     sql = """SELECT code, parent_id FROM account_account"""
    #     self.env.cr.execute(sql)
    #     datas = self.env.cr.fetchall()
    #     account_value_dict = {}     # {account_code: [debit, credit]}
    #     for data in datas:
    #         account_value_dict[data[0]] = [0, 0]
    #
    #     struct_data = {}    # {level: [(aa.code, aa.name, debit, credit, level, parent_id), ...]
    #     for data in sql_data:
    #         level_data = struct_data.setdefault(data[4], [])
    #         level_data.append(data)
    #
    #     for level in sorted(struct_data, reverse=True):
    #         for data in struct_data[level]:
    #             account_value_dict[data[0]] =


